package com.crud.generator.service;

import com.crud.generator.model.ColumnMetadata;
import com.crud.generator.model.TableMetadata;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库逆向工程服务
 * 从现有MySQL数据库读取表结构
 * 
 * @author CRUD-Generator
 */
@Service
@Slf4j
public class DatabaseReverseService {

    /**
     * 从数据库读取表结构
     * 
     * @param host 数据库主机
     * @param port 数据库端口
     * @param database 数据库名
     * @param username 用户名
     * @param password 密码
     * @param tableName 表名
     * @return 表元数据
     */
    public TableMetadata readTableStructure(String host, int port, String database, 
                                           String username, String password, String tableName) {
        
        String url = String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai", 
                                 host, port, database);
        
        log.info("连接数据库读取表结构: {}", url);
        
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            return extractTableMetadata(connection, database, tableName);
        } catch (SQLException e) {
            log.error("读取数据库表结构失败", e);
            throw new RuntimeException("读取数据库表结构失败: " + e.getMessage(), e);
        }
    }
    
    /**
     * 获取数据库中的所有表名
     * 
     * @param host 数据库主机
     * @param port 数据库端口
     * @param database 数据库名
     * @param username 用户名
     * @param password 密码
     * @return 表名列表
     */
    public List<String> getTableNames(String host, int port, String database, 
                                     String username, String password) {
        
        String url = String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai", 
                                 host, port, database);
        
        List<String> tableNames = new ArrayList<>();
        
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(database, null, "%", new String[]{"TABLE"});
            
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                tableNames.add(tableName);
            }
            
            log.info("获取到{}个表: {}", tableNames.size(), tableNames);
            return tableNames;
            
        } catch (SQLException e) {
            log.error("获取表名列表失败", e);
            throw new RuntimeException("获取表名列表失败: " + e.getMessage(), e);
        }
    }
    
    /**
     * 提取表元数据
     */
    private TableMetadata extractTableMetadata(Connection connection, String database, String tableName) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        
        // 获取表信息
        TableMetadata tableMetadata = new TableMetadata();
        tableMetadata.setTableName(tableName);
        
        // 获取表注释
        String tableComment = getTableComment(connection, database, tableName);
        tableMetadata.setTableComment(tableComment);
        
        // 获取列信息
        List<ColumnMetadata> columns = new ArrayList<>();
        ResultSet columnsRS = metaData.getColumns(database, null, tableName, "%");
        
        while (columnsRS.next()) {
            ColumnMetadata column = new ColumnMetadata();
            column.setColumnName(columnsRS.getString("COLUMN_NAME"));
            column.setDataType(columnsRS.getString("TYPE_NAME"));
            column.setNullable("YES".equals(columnsRS.getString("IS_NULLABLE")));
            column.setColumnComment(columnsRS.getString("REMARKS"));
            column.setDefaultValue(columnsRS.getString("COLUMN_DEF"));
            
            // 设置长度和精度
            int columnSize = columnsRS.getInt("COLUMN_SIZE");
            int decimalDigits = columnsRS.getInt("DECIMAL_DIGITS");
            
            if (columnSize > 0) {
                column.setLength(columnSize);
            }
            if (decimalDigits > 0) {
                column.setScale(decimalDigits);
            }
            
            // 检查是否为自增字段
            String isAutoIncrement = columnsRS.getString("IS_AUTOINCREMENT");
            column.setAutoIncrement("YES".equals(isAutoIncrement));
            
            // 映射到Java类型
            column.setJavaType(mapToJavaType(column.getDataType()));
            column.setJavaFieldName(toJavaFieldName(column.getColumnName()));
            column.setJavaPropertyName(toJavaPropertyName(column.getColumnName()));
            
            columns.add(column);
        }
        
        tableMetadata.setColumns(columns);
        
        // 获取主键信息
        setPrimaryKeys(metaData, database, tableName, tableMetadata);
        
        log.info("成功提取表{}的元数据，包含{}个字段", tableName, columns.size());
        return tableMetadata;
    }
    
    /**
     * 获取表注释
     */
    private String getTableComment(Connection connection, String database, String tableName) {
        String sql = "SELECT TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
        
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, database);
            stmt.setString(2, tableName);
            
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getString("TABLE_COMMENT");
            }
        } catch (SQLException e) {
            log.warn("获取表注释失败: {}", e.getMessage());
        }
        
        return null;
    }
    
    /**
     * 设置主键信息
     */
    private void setPrimaryKeys(DatabaseMetaData metaData, String database, String tableName, TableMetadata tableMetadata) throws SQLException {
        ResultSet primaryKeys = metaData.getPrimaryKeys(database, null, tableName);
        List<ColumnMetadata> primaryKeyColumns = new ArrayList<>();
        
        while (primaryKeys.next()) {
            String columnName = primaryKeys.getString("COLUMN_NAME");
            
            // 在columns中找到对应的列并标记为主键
            for (ColumnMetadata column : tableMetadata.getColumns()) {
                if (column.getColumnName().equals(columnName)) {
                    column.setPrimaryKey(true);
                    primaryKeyColumns.add(column);
                    break;
                }
            }
        }
        
        // 设置主键信息
        if (!primaryKeyColumns.isEmpty()) {
            tableMetadata.setPrimaryKeys(primaryKeyColumns);
            tableMetadata.setPrimaryKey(primaryKeyColumns.get(0)); // 设置第一个作为主要主键
            tableMetadata.setCompositePrimaryKey(primaryKeyColumns.size() > 1);
        }
    }
    
    /**
     * 数据库类型映射到Java类型
     */
    private String mapToJavaType(String dbType) {
        String upperType = dbType.toUpperCase();
        
        switch (upperType) {
            case "BIGINT":
                return "Long";
            case "INT":
            case "INTEGER":
            case "TINYINT":
            case "SMALLINT":
            case "MEDIUMINT":
                return "Integer";
            case "FLOAT":
                return "Float";
            case "DOUBLE":
            case "REAL":
                return "Double";
            case "DECIMAL":
            case "NUMERIC":
                return "BigDecimal";
            case "VARCHAR":
            case "CHAR":
            case "TEXT":
            case "TINYTEXT":
            case "MEDIUMTEXT":
            case "LONGTEXT":
            case "JSON":
            case "ENUM":
            case "SET":
                return "String";
            case "DATE":
                return "LocalDate";
            case "TIME":
                return "LocalTime";
            case "DATETIME":
            case "TIMESTAMP":
                return "LocalDateTime";
            case "BIT":
            case "BOOLEAN":
                return "Boolean";
            case "BLOB":
            case "BINARY":
            case "VARBINARY":
                return "byte[]";
            default:
                return "String";
        }
    }
    
    /**
     * 转换为Java字段名（驼峰命名）
     */
    private String toJavaFieldName(String columnName) {
        if (columnName == null || columnName.isEmpty()) {
            return columnName;
        }
        
        StringBuilder result = new StringBuilder();
        boolean nextUpper = false;
        
        for (char c : columnName.toCharArray()) {
            if (c == '_') {
                nextUpper = true;
            } else {
                if (nextUpper) {
                    result.append(Character.toUpperCase(c));
                    nextUpper = false;
                } else {
                    result.append(Character.toLowerCase(c));
                }
            }
        }
        
        return result.toString();
    }
    
    /**
     * 转换为Java属性名（首字母大写的驼峰命名）
     */
    private String toJavaPropertyName(String columnName) {
        String fieldName = toJavaFieldName(columnName);
        if (fieldName == null || fieldName.isEmpty()) {
            return fieldName;
        }
        
        return Character.toUpperCase(fieldName.charAt(0)) + fieldName.substring(1);
    }
}